{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(working-with-data)=\n",
    "# Working with Data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "The previous chapter was just a quick tour of what can be done with a single tabular dataset (a 'dataframe'). In this chapter, we'll go deeper into working with data.\n",
    "\n",
    "The ability to extract, clean, and analyse data is one of the core skills any economist needs. Fortunately, the (open source) tools that are available for data analysis have improved enormously in recent years, and working with them can be a delight--even the most badly formatted data can be beaten into shape.\n",
    "\n",
    "In this chapter, you'll get really good introduction to the [**pandas**](https://pandas.pydata.org/) package, the core data manipulation library in Python. The name is derived from 'panel data' but it's suited to any tabular data, and can be used to work with more complex data structures too. We *won't* cover reading in or writing data here; see the next chapter for that.\n",
    "\n",
    "This chapter is hugely indebted to the fantastic [Python Data Science Handbook](https://jakevdp.github.io/PythonDataScienceHandbook/) by Jake Vanderplas. Remember, if you get stuck with pandas, there is brilliant [documentation](https://pandas.pydata.org/docs/user_guide/index.html) and a fantastic set of [introductory tutorials](https://pandas.pydata.org/pandas-docs/stable/getting_started/intro_tutorials/index.html) on their website. These notes are heavily indebted to those introductory tutorials.\n",
    "\n",
    "This chapter uses the **pandas**, **seaborn**, and **numpy** packages. If you're running this code, you may need to install these packages, which you can do using either `conda install packagename` or `pip install packagename` on your computer's command line. (If you're not sure what a command line or terminal is, take a quick look at the basics of coding chapter.)\n",
    "\n",
    "\n",
    "### Using tidy data\n",
    "\n",
    "As an aside, if you're working with tabular data, it's good to try and use a so-called 'tidy data' format. This is data with one observation per row, and one variable per column, like so:\n",
    "\n",
    "![](https://d33wubrfki0l68.cloudfront.net/6f1ddb544fc5c69a2478e444ab8112fb0eea23f8/91adc/images/tidy-1.png)\n",
    "\n",
    "Tidy data aren't going to be appropriate *every* time and in every case, but they're a really, really good default for tabular data. Once you use it as your default, it's easier to think about how to perform subsequent operations. Some plotting libraries, such as **seaborn**, take that your data are in tidy format as a given. And many operations that you can perform on dataframes (the objects that hold tabular data within many programming languages) are easier when you have tidy data. If you're writing out data to file to share, putting it in tidy format is a really good idea.\n",
    "\n",
    "Of course, *getting* your messy dataset into a tidy format may take a bit of work... but we're about to enter the exciting world of coding for data analysis and the tools you'll see in the rest of this chapter will help you to 'wrangle' even the most messy of datasets.\n",
    "\n",
    "Having said that tidy data are great, and they are, one of standard data library **pandas**' advantages relative to other data analysis libraries is that it isn't *too* tied to tidy data and can navigate awkward non-tidy data manipulation tasks happily too."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataframes and series\n",
    "\n",
    "Let's start with the absolute basics. The most basic **pandas** object is a dataframe. A DataFrame is a 2-dimensional data structure that can store data of different types (including characters, integers, floating point values, categorical data, even lists) in columns. \n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/01_table_dataframe.svg)\n",
    "\n",
    "We'll now import some useful packages, set a random number seed (some examples use randomly generated data), set up some nice plot settings, and load a dataset.\n",
    "\n",
    "We'll look at a dataframe of the *penguins* dataset. To show just the first 5 rows, I'll be using the `head()` method (there's also a `tail()` method).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import os\n",
    "\n",
    "# Set seed for reproducibility\n",
    "np.random.seed(10)\n",
    "# Plot settings\n",
    "plt.style.use(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/raw/main/plot_style.txt\"\n",
    ")\n",
    "\n",
    "df = sns.load_dataset(\"penguins\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What just happened? We loaded a pandas dataframe called `df` and showed its contents. You can see the column names in bold, and the index on the left hand side. Just to double check it *is* a pandas dataframe, we can call type on this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And if we want a bit more information about what we imported (including the datatypes of the columns):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember that everything in Python is an object, and our dataframe is no exception. Each dataframe is made up of a set of series that, in a dataframe, become columns: but you can turn a single series into a dataframe too. \n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/01_table_series.svg)\n",
    "\n",
    "Let's see a couple of ways of creating some series from raw data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "hide-output"
    ]
   },
   "outputs": [],
   "source": [
    "# From a list:\n",
    "s1 = pd.Series([1.0, 6.0, 19.0, 2.0])\n",
    "print(s1)\n",
    "print(\"\\n\")\n",
    "# From a dictionary\n",
    "population_dict = {\n",
    "    \"California\": 38332521,\n",
    "    \"Texas\": 26448193,\n",
    "    \"New York\": 19651127,\n",
    "    \"Florida\": 19552860,\n",
    "    \"Illinois\": 12882135,\n",
    "}\n",
    "s2 = pd.Series(population_dict)\n",
    "print(s2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that in each case there is no column name (because this is a series, not a dataframe), and there *is* an index. The index is automatically created if we don't specify it; in the third example, by passing a dictionary we implicitly asked for the index to be the locations we supplied. \n",
    "\n",
    "If you ever need to get the data 'out' of a series or dataframe, you can just call the `values` method on the object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s2.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you ever want to turn a series into a dataframe, just called `pd.DataFrame(series)` on it. Note that while series have an index and an object name (eg `s2` above), they don't have any column labels because they only have one column.\n",
    "\n",
    "Now let's try creating our own dataframe with more than one column of data using a *dictionary*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"A\": 1.0,\n",
    "        \"B\": pd.Series(1, index=list(range(4)), dtype=\"float32\"),\n",
    "        \"C\": [3] * 4,\n",
    "        \"D\": pd.Categorical([\"test\", \"train\", \"test\", \"train\"]),\n",
    "        \"E\": \"foo\",\n",
    "    }\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember, curly brackets in the format `{key: value}` denote a dictionary. In the above example, the `pd.DataFrame()` function understands that any single value entries in the dictionary that is passed, such as `{'A': 1.}`, should be repeated as many times as are needed to match the longest series in the dictionary (4 in the above example).\n",
    "\n",
    "Another way to create dataframes is to pass a bunch of series (note that `index`, `columns`, and `dtype` are optional--you can just specify the data):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    data=np.reshape(range(36), (6, 6)),\n",
    "    index=[\"a\", \"b\", \"c\", \"d\", \"e\", \"f\"],\n",
    "    columns=[\"col\" + str(i) for i in range(6)],\n",
    "    dtype=float,\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that `reshape` takes an input and puts it into a given shape (a 6 by 6 matrix in the above example)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Values, columns, and index\n",
    "\n",
    "You'll have seen that there are three different things that make up a dataframe: the values that are in the cells, the column names, and the index. The column and index can take on values that are the same as the values in a dataframe do; string, int, float, datetime, and more. It's pretty obvious what role the columns play: they keep track of the name of different sets of values. But for people who may have seen other dataframe-like libraries, the role played by the index may be less familiar. The easiest way to think about a **pandas** index is that it does for row values what the columns do for columnar values: it's a way of keeping track of what individual roles are and it *doesn't* get used for calculations (just as summing a column ignores the name of the row).\n",
    "\n",
    "Here's an example to show this. Let's first create a simple dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    data={\n",
    "        \"col0\": [0, 0, 0, 0],\n",
    "        \"col1\": [0, 0, 0, 0],\n",
    "        \"col2\": [0, 0, 0, 0],\n",
    "        \"col3\": [\"a\", \"b\", \"b\", \"a\"],\n",
    "        \"col4\": [\"alpha\", \"gamma\", \"gamma\", \"gamma\"],\n",
    "    },\n",
    "    index=[\"row\" + str(i) for i in range(4)],\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we add one to the integer columns in the dataframe, this is what we get (note we're not saving the result):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"col0\", \"col1\", \"col2\"]] + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's use `col0` as our index instead of the original labels we created and add one to the remaining numeric columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(\"col0\")\n",
    "df[[\"col1\", \"col2\"]] = df[[\"col1\", \"col2\"]] + 1\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was a column name has become an index name (which you can change with `df.index.name='newname'`) and, when we do add one, it isn't applied to the index. Even though their datatype is `int`, for integer, the index entries are now acting as a label for each row--not as values in the dataframe.\n",
    "\n",
    "An index can be useful for keeping track of what's going on, and it's particularly convenient for some datetime operations.\n",
    "\n",
    "Whenever you use `groupby` (and some other operations), the columns you use to perform the operation are set as the index of the returned dataframe (you can have multiple index columns). To get back those back to being columns, use the `reset_index()` method like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"col3\", \"col4\"]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"col3\", \"col4\"]).sum().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datatypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has some built-in datatypes (some are the basic Python datatypes) that will make your life a *lot* easier if you work with them. Why bother specifying datatypes? Languages like Python let you get away with having pretty much anything in your columns. But this can be a problem: sometimes you'll end up mixing integers, strings, the generic 'object' datatype, and more by mistake. By ensuring that columns conform to a datatype, you can save yourself from some of the trials that come with these mixed datatypes. Some of the most important datatypes for dataframe are string, float, categorical, datetime, int, and boolean. \n",
    "\n",
    "Typically, you'll read in a dataset where the dataypes of the columns are a mess. One of the first things you'll want to do is sort these out. Here's an example dataset showing how to set the datatypes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    [\"string1\", \"string2\"],\n",
    "    [1.2, 3.4],\n",
    "    [\"type_a\", \"type_b\"],\n",
    "    [\"01-01-1999\", \"01-01-2000\"],\n",
    "    [1, 2],\n",
    "    [0, 1],\n",
    "]\n",
    "columns = [\n",
    "    \"string_col\",\n",
    "    \"double_col\",\n",
    "    \"category_col\",\n",
    "    \"datetime_col\",\n",
    "    \"integer_col\",\n",
    "    \"bool_col\",\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data=np.array(data).T, columns=columns)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the data type for all of these columns is the generic 'Object' (you can see this from the `Dtype` column that is printed when you use `df.info()`). Let's fix that:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.assign(\n",
    "    string_col=df[\"string_col\"].astype(\"string\"),\n",
    "    double_col=df[\"double_col\"].astype(\"double\"),\n",
    "    category_col=df[\"category_col\"].astype(\"category\"),\n",
    "    datetime_col=df[\"datetime_col\"].astype(\"datetime64[ns]\"),\n",
    "    integer_col=df[\"integer_col\"].astype(\"int\"),\n",
    "    bool_col=df[\"bool_col\"].astype(\"bool\"),\n",
    ")\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you're creating a series or dataframe from scratch, here's how to start off with these datatypes:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "str_s = pd.Series([\"string1\", \"string2\"], dtype=\"string\")\n",
    "float_s = pd.Series([1.2, 3.4], dtype=float)\n",
    "cat_s = pd.Series([\"type_a\", \"type_b\"], dtype=\"category\")\n",
    "date_s = pd.Series([\"01-01-1999\", \"01-01-2000\"], dtype=\"datetime64[ns]\")\n",
    "int_s = pd.Series([1, 2], dtype=int)\n",
    "bool_s = pd.Series([True, False], dtype=bool)\n",
    "\n",
    "df = pd.concat([str_s, float_s, cat_s, date_s, int_s, bool_s], axis=1)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorical variables\n",
    "\n",
    "Categorical variables can be especially useful and there are a couple of convenience functions that allow you to create them from other types of columns. Cut splits input data into a given number of (evenly spaced) bins that you can optionally give names to via the `labels=` keyword. The default behaviour is for the order of the labels to matter so that, in the below example, the bins will be evenly spaced from the smallest value to the largest, with the smallest receiving the label `bad`.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut([1, 7, 5, 4, 6, 3], 3, labels=[\"bad\", \"medium\", \"good\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also pass the bins directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut([1, 7, 5, 4, 6, 3], bins=[-5, 0, 5, 10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another useful function is qcut, which provides a categorical breakdown according to a given number of quantiles (eg 4 produces quartiles):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.qcut(range(1, 10), 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing and slicing\n",
    "\n",
    "Now you know how to put data in a dataframe, how do you access the bits of it you need? There are various ways. If you want to access an entire column, the syntax is very simple; `df['columname']` (you can also use `df.columname`).\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/03_subset_columns.svg)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    data=np.reshape(range(36), (6, 6)),\n",
    "    index=[\"a\", \"b\", \"c\", \"d\", \"e\", \"f\"],\n",
    "    columns=[\"col\" + str(i) for i in range(6)],\n",
    "    dtype=float,\n",
    ")\n",
    "df[\"col1\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To access a particular row, it's `df.loc['rowname']` or `df.loc[['rowname1', 'rowname1']]`.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/03_subset_rows.svg)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[[\"a\", \"b\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As well as the `.loc` method, there is the `.iloc` method that accesses rows or columns based on their position, for example `df.iloc[i, :]` for the ith row and `df.iloc[:, j]` for the jth column (but remember the numbers start from zero).\n",
    "\n",
    "To access an individual value from within the dataframe, we have two options: pass an index value and a column name to `.loc[rowname, columnname]` or retrieve the value by using its position using `.iloc[row, column]`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using .loc\n",
    "print(df.loc[\"b\", \"col1\"])\n",
    "# Using .iloc\n",
    "print(df.iloc[1, 0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With all of these different ways to access values in dataframes, it can get confusing. These are the different ways to get the first column of a dataframe (when that first column is called `column` and the dataframe is `df`):\n",
    "\n",
    "- `df.column`\n",
    "- `df[\"column\"]`\n",
    "- `df.loc[:, \"column\"]`\n",
    "- `df.iloc[:, 0]`\n",
    "\n",
    "The ways to access rows are similar (here assuming the first row is called `row`):\n",
    "\n",
    "- `df.loc[\"row\", :]`\n",
    "- `df.iloc[0, :]`\n",
    "\n",
    "And to access the first value (ie the value in first row, first column):\n",
    "\n",
    "- `df.column[0]`\n",
    "- `df[\"column\"][0]`\n",
    "- `df.iloc[0, 0]`\n",
    "- `df.loc[\"row\", \"column\"]`\n",
    "\n",
    "In the above examples, square brackets are instructions about *where* to grab bits from the dataframe. They are a bit like an address system for values within a dataframe.\n",
    "\n",
    "Square brackets *also* denote lists though. So if you want to select *multiple* columns or rows, you might see syntax like this:\n",
    "\n",
    "`df.loc[[\"row0\", \"row1\"], [\"column0\", \"column2\"]]`\n",
    "\n",
    "which picks out two rows and two columns via the lists `[\"row0\", \"row1\"]` and `[\"column0\", \"column2\"]`. Because there are lists alongside the usual system of selecting values, there are two sets of square brackets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So often what we really want is a subset of values (as opposed to *all* values or just *one* value). This is where *slicing* comes in. If you've looked at the Basics of Coding chapter, you'll know a bit about slicing and indexing already, but we'll cover the basics here too.\n",
    "\n",
    "The syntax for slicing is similar to what we've seen already: there are two methods `.loc` to access items by name, and `.iloc` to access them by position. The syntax for the former is `df.loc[start:stop:step, start:stop:step]`, where the first position is index name and the second is column name (and the same applies for numbers and `df.iloc`). Let's see an example using the storms dataset, and do some cuts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\"a\":\"f\":2, \"col1\":\"col3\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, slicing even works on names! By asking for rows `'a':'f':2`, we get every other row from 'a' to 'f' (inclusive). Likewise, for columns, we asked for every column between `col1` and `col3` (inclusive). `iloc` works in a very similar way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[1:, :-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, we asked for everything from row 1 onwards, and everything up to (but excluding) the last column.\n",
    "\n",
    "It's not just strings and positions that can be sliced though, here's an example using *dates* (pandas support for dates is truly excellent):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = pd.date_range(\"1/1/2000\", periods=12, freq=\"Q\")\n",
    "df = pd.DataFrame(np.random.randint(0, 10, (12, 5)), index=index, columns=list(\"ABCDE\"))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's do some slicing!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\"2000-01-01\":\"2002-01-01\", :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two important points to note here: first, pandas doesn't mind that we supplied a date that didn't actually exist in the index. It worked out that by '2000-01-01' we meant a datetime and compared the values of the index to that datetime in order to decide what rows to return from the dataframe. The second thing to notice is the use of `:` for the column names; this explicitly says 'give me all the columns'."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Operations on dataframes\n",
    "\n",
    "Columns in dataframes can undergo all the usual arithmetic operations you'd expect of addition, multiplication, division, and so on. If the underlying datatypes of two columns have a group operation, then the dataframe columns will use that. \n",
    "\n",
    "![](https://github.com/pandas-dev/pandas/raw/059c8bac51e47d6eaaa3e36d6a293a22312925e6/doc/source/_static/schemas/05_newcolumn_1.svg)\n",
    "\n",
    "The results of these manipulations can just be saved as a new series, eg, `new_series = df['A'] + df['B']` or created as a new column of the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"new_col\"] = df[\"A\"] * (df[\"B\"] ** 2) + 1\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Boolean variables and strings have group operations (eg concatenation is via `+` with strings), and so work well with column operations too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\"a\": [1, 0, 1], \"b\": [0, 1, 1], \"c\": [0, 1, 1], \"d\": [1, 1, 0]}, dtype=bool\n",
    ")\n",
    "print(df)\n",
    "print(\"\\n a and c:\\n\")\n",
    "print(df[\"a\"] & df[\"c\"])\n",
    "print(\"\\n b or d:\\n\")\n",
    "print(df[\"b\"] | df[\"d\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More complex operations on whole dataframes are supported, but if you're doing very heavy lifting you might want to just switch to using numpy arrays (**numpy** is basically Matlab in Python). As examples though, you can transpose and exponentiate easily:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randint(0, 5, (3, 5)), columns=list(\"ABCDE\"))\n",
    "print(\"\\n Dataframe:\")\n",
    "print(df)\n",
    "print(\"\\n Exponentiation:\")\n",
    "print(np.exp(df))\n",
    "print(\"\\n Transpose:\")\n",
    "print(df.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregation\n",
    "\n",
    "**pandas** has built-in aggregation functions such as\n",
    "\n",
    "| Aggregation      | Description |\n",
    "| ----------- | ----------- |\n",
    "| `count()`      | Number of items       |\n",
    "| `first()`, `last()` | \tFirst and last item |\n",
    "| `mean()`, `median()` |\tMean and median |\n",
    "| `min()`, `max()` |\tMinimum and maximum |\n",
    "| `std()`, `var()` |\tStandard deviation and variance |\n",
    "| `mad()` |\tMean absolute deviation |\n",
    "| `prod()` |\tProduct of all items |\n",
    "| `sum()`\t| Sum of all items |\n",
    "| `value_counts()` | Counts of unique values |\n",
    "\n",
    "these can applied to all entries in a dataframe, or optionally to rows or columns using `axis=0` or `axis=1` respectively.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sum(axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Split, apply, and combine\n",
    "\n",
    "Splitting a dataset, applying a function, and combining the results are three key operations that we'll want to use again and again. Splitting means differentiating between rows or columns of data based on some conditions, for instance different categories or different values. Applying means applying a function, for example finding the mean or sum. Combine means putting the results of these operations back into the dataframe, or into a variable. The figure gives an example\n",
    "\n",
    "![](https://jakevdp.github.io/PythonDataScienceHandbook/figures/03.08-split-apply-combine.png)\n",
    "\n",
    "Note that the 'combine' part doesn't always have to result in a new dataframe; it could create new columns in an existing dataframe.\n",
    "\n",
    "Let's first see a really simple example of splitting a dataset into groups and finding the mean across those groups using the *penguins* dataset. We'll group the data by island and look at the means. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sns.load_dataset(\"penguins\")\n",
    "df.groupby(\"island\").mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The aggregations from the previous part all work on grouped data. An example is `df['body_mass_g'].groupby('island').std()` for the standard deviation of body mass by island.\n",
    "\n",
    "You can also pass other functions via the `agg` method (short for aggregation). Here we pass two numpy functions:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(\"species\").agg([np.mean, np.std])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Multiple aggregations can also be performed at once on the entire dataframe by using a dictionary to map columns into functions. You can also group by as many variables as you like by passing the groupby method a list of variables. Here's an example that combines both of these features:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"species\", \"island\"]).agg({\"body_mass_g\": \"sum\", \"bill_length_mm\": \"mean\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes, inheriting the column names becomes problematic. There's a slightly fussy syntax to help with that:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"species\", \"island\"]).agg(\n",
    "    count_bill=(\"bill_length_mm\", \"count\"),\n",
    "    mean_bill=(\"bill_length_mm\", \"mean\"),\n",
    "    std_flipper=(\"flipper_length_mm\", np.std),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, you should know about the `apply` method, which takes a function and applies it to a given axis (`axis=0` for index, `axis=1` for columns) or column. The simple example below shows how it works, though in practice you'd just use `df['body_mass_kg'] = df['body_mass_g]/1e3` to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def g_to_kg(mass_in_g):\n",
    "    return mass_in_g / 1e3\n",
    "\n",
    "\n",
    "df[\"mass_in_kg\"] = df[\"body_mass_g\"].apply(g_to_kg)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter, transform, apply, and assign\n",
    "\n",
    "### Filter\n",
    "\n",
    "Filtering does exactly what it sounds like, but it can make use of group-by commands. In the example below, all but one species is filtered out.\n",
    "\n",
    "In the example below, `filter` passes a grouped version of the dataframe into the `filter_func` we've defined (imagine that a dataframe is passed for each group). Because the passed variable is a dataframe, and variable `x` is defined in the function, the `x` within `filter_func` body behaves like our dataframe--including having the same columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_func(x):\n",
    "    return x[\"bill_length_mm\"].mean() > 48\n",
    "\n",
    "\n",
    "df.groupby(\"species\").filter(filter_func).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transform\n",
    "\n",
    "Transforms return a transformed version of the data that has the same shape as the input. This is useful when creating new columns that depend on some grouped data, for instance creating group-wise means. Here's an example using the datetime group to subtract a yearly mean. First let's create some synthetic data with some data, a datetime index, and some groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = pd.date_range(\"1/1/2000\", periods=10, freq=\"Q\")\n",
    "data = np.random.randint(0, 10, (10, 2))\n",
    "df = pd.DataFrame(data, index=index, columns=[\"values1\", \"values2\"])\n",
    "df[\"type\"] = np.random.choice([\"group\" + str(i) for i in range(3)], 10)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we take the yearly means by type. `pd.Grouper(freq='A')` is an instruction to take the `A`nnual mean using the given datetime index. You can group on as many coloumns and/or index properties as you like: this example groups by a property of the datetime index and on the `type` column, but performs the computation on the `values1` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"v1_demean_yr_type\"] = df.groupby([pd.Grouper(freq=\"A\"), \"type\"])[\n",
    "    \"values1\"\n",
    "].transform(lambda x: x - x.mean())\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll have seen there's a `lambda` keyword here. Lambda (or anonymous) functions have a rich history in mathematics, and were used by scientists such as Church and Turing to create proofs about what is computable *before electronic computers existed*. They can be used to define compact functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "multiply_plus_one = lambda x, y: x * y + 1\n",
    "multiply_plus_one(3, 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Apply\n",
    "\n",
    "\n",
    "Both regular functions and lambda functions can be used with the more general apply method, which takes a function and applies it to a given axis (`axis=0` for index, `axis=1` for columns):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"val1_times_val2\"] = df.apply(lambda row: row[\"values1\"] * row[\"values2\"], axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, the much easier way to do this very common operation is `df['val1_times_val2'] = df['values1']*df['values2']`, but there are times when you need to run more complex functions element-wise and, for those, `apply` is really useful."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assign\n",
    "\n",
    "Assign is a method that allows you to return a new object with all the original columns in addition to new ones. Existing columns that are re-assigned will be overwritten. This is *really* useful when you want to perform a bunch of operations together in a concise way and keep the original columns. For instance, to demean the 'values1' column by year-type and to recompute the 'val1_times_val2' column using the newly demeaned 'values1' column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.assign(\n",
    "    values1=(\n",
    "        df.groupby([pd.Grouper(freq=\"A\"), \"type\"])[\"values1\"].transform(\n",
    "            lambda x: x - x.mean()\n",
    "        )\n",
    "    ),\n",
    "    val1_times_val2=lambda x: x[\"values1\"] * x[\"values2\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time series, resampling, and rolling windows\n",
    "\n",
    "The support for time series and the datetime type is excellent in pandas. It is very easy to manipulate datetimes. The [relevant part](https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html) of the documentation has more info; here we'll just see a couple of the most important bits. First, let's create some synthetic data to work with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def recursive_ts(n, x=0.05, beta=0.6, alpha=0.2):\n",
    "    shock = np.random.normal(loc=0, scale=0.6)\n",
    "    if n == 0:\n",
    "        return beta * x + alpha + shock\n",
    "    else:\n",
    "        return beta * recursive_ts(n - 1, x=x) + alpha + shock\n",
    "\n",
    "\n",
    "t_series = np.cumsum([recursive_ts(n) for n in range(12)])\n",
    "index = pd.date_range(\"1/1/2000\", periods=12, freq=\"M\")\n",
    "df = pd.DataFrame(t_series, index=index, columns=[\"values\"])\n",
    "df.loc[\"2000-08-31\", \"values\"] = np.nan\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's imagine that there are a number of issues with this time series. First, it's been recorded wrong: it actually refers to the start of the next month, not the end of the previous as recorded; second, there's a missing number we want to interpolate; third, we want to take the difference of it to get to something stationary; fourth, we'd like to add a lagged column. We can do all of those things!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change freq to next month start\n",
    "df.index += pd.tseries.offsets.DateOffset(days=1)\n",
    "\n",
    "df[\"values\"] = df[\"values\"].interpolate(method=\"time\")\n",
    "df[\"diff_values\"] = df[\"values\"].diff(1)\n",
    "df[\"lag_diff_values\"] = df[\"diff_values\"].shift(1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two other useful time series functions to be aware of are `resample` and `rolling`. `resample` can upsample or downsample time series. Downsampling is by aggregation, eg `df['values].resample('Q').mean()` to downsample to quarterly ('Q') frequency by taking the mean within each quarter. Upsampling involves a choice about how to fill in the missing values; examples of options are `bfill` (backfill) and `ffill` (forwards fill).\n",
    "\n",
    "Rolling is for taking rolling aggregations, as you'd expect; for example, the 3-month rolling mean of our first difference time series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"diff_values\"].rolling(3).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Method chaining\n",
    "\n",
    "Sometimes, rather than splitting operations out into multiple lines, it can be more concise and clear to chain methods together. A typical time you might do this is when reading in a dataset and perfoming all of the initial cleaning. Tom Augsperger has a [great tutorial](https://tomaugspurger.github.io/method-chaining) on this, which I've reproduced parts of here. For more info on the `pipe` function used below, check out these short [video tutorials](https://calmcode.io/pandas-pipe/introduction.html).\n",
    "\n",
    "To chain methods together, both the input and output must be a pandas dataframe. Many functions already do input and output these, for example the `df.rename(columns={'old_col': 'new_col'})` takes in `df` and outputs a dataframe with one column name changed.\n",
    "\n",
    "But occassionally, we'll want to use a function that we've defined (rather than an already existing one). For that, we need the `pipe` method; it 'pipes' the result of one operation to the next operation. When objects are being passed through multiple functions, this can be much clearer. Compare, for example,\n",
    "\n",
    "```python\n",
    "f(g(h(df), g_arg=a), f_arg=b)\n",
    "```\n",
    "\n",
    "that is, dataframe `df` is being passed to function `h`, and the results of that are being passed to a function `g` that needs a key word argument `g_arg`, and the results of *that* are being passed to a function `f` that needs keyword argument `f_arg`. The nested structure is barely readable. Compare this with\n",
    "\n",
    "```python\n",
    "(df.pipe(h)\n",
    "   .pipe(g, g_arg=a)\n",
    "   .pipe(f, f_arg=b)\n",
    ")  \n",
    "```\n",
    "\n",
    "Let's see a method chain in action on a real dataset so you get a feel for it. We'll use 1,000 rows of flight data from BTS (a popular online dataset for demos of data cleaning!). TODO use github path. (For further info on method chaining in Python, [see these videos](https://calmcode.io/method-chains/introduction.html)--but be aware they assume advanced knowledge of the language.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/raw/main/data/flights1kBTS.csv\",\n",
    "    index_col=0,\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll try and do a number of operations in one go: putting column titles in lower case, discarding useless columns, creating precise depature and arrival times, turning some of the variables into categoricals, creating a demeaned delay time, and creating a new categorical column for distances according to quantiles that will be called 'near', 'less near', 'far', and 'furthest'. Some of these operations require a separate function, so we first define those. When we do the cleaning, we'll pipe our dataframe to those functions (optionally passing any arguments)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_city_name(df):\n",
    "    \"\"\"\n",
    "    Chicago, IL -> Chicago for origin_city_name and dest_city_name\n",
    "    \"\"\"\n",
    "    cols = [\"origin_city_name\", \"dest_city_name\"]\n",
    "    city = df[cols].apply(lambda x: x.str.extract(\"(.*), \\w{2}\", expand=False))\n",
    "    df = df.copy()\n",
    "    df[[\"origin_city_name\", \"dest_city_name\"]] = city\n",
    "    return df\n",
    "\n",
    "\n",
    "def time_to_datetime(df, columns):\n",
    "    \"\"\"\n",
    "    Combine all time items into datetimes.\n",
    "\n",
    "    2014-01-01,0914 -> 2014-01-01 09:14:00\n",
    "    \"\"\"\n",
    "    df = df.copy()\n",
    "\n",
    "    def converter(col):\n",
    "        timepart = (\n",
    "            col.astype(str)\n",
    "            .str.replace(\"\\.0$\", \"\")  # NaNs force float dtype\n",
    "            .str.pad(4, fillchar=\"0\")\n",
    "        )\n",
    "        return pd.to_datetime(\n",
    "            df[\"fl_date\"]\n",
    "            + \" \"\n",
    "            + timepart.str.slice(0, 2)\n",
    "            + \":\"\n",
    "            + timepart.str.slice(2, 4),\n",
    "            errors=\"coerce\",\n",
    "        )\n",
    "\n",
    "    df[columns] = df[columns].apply(converter)\n",
    "    return df\n",
    "\n",
    "\n",
    "df = (\n",
    "    df.drop([x for x in df.columns if \"Unnamed\" in x], axis=1)\n",
    "    .rename(columns=str.lower)\n",
    "    .pipe(extract_city_name)\n",
    "    .pipe(time_to_datetime, [\"dep_time\", \"arr_time\"])\n",
    "    .assign(\n",
    "        fl_date=lambda x: pd.to_datetime(x[\"fl_date\"]),\n",
    "        dest=lambda x: pd.Categorical(x[\"dest\"]),\n",
    "        origin=lambda x: pd.Categorical(x[\"origin\"]),\n",
    "        tail_num=lambda x: pd.Categorical(x[\"tail_num\"]),\n",
    "        arr_delay=lambda x: pd.to_numeric(x[\"arr_delay\"]),\n",
    "        op_unique_carrier=lambda x: pd.Categorical(x[\"op_unique_carrier\"]),\n",
    "        arr_delay_demean=lambda x: x[\"arr_delay\"] - x[\"arr_delay\"].mean(),\n",
    "        distance_group=lambda x: (\n",
    "            pd.qcut(x[\"distance\"], 4, labels=[\"near\", \"less near\", \"far\", \"furthest\"])\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pyjanitor and more extensive method chaining\n",
    "\n",
    "Although there's enough support for functional style method chaining to get by, you might find that doing this or that operation isn't always as efficient or as concise as you'd like. Let's look at two examples where this bites and what a wonderful little extension to **pandas** called [**Pyjanitor**](https://pyjanitor.readthedocs.io) does about it.\n",
    "\n",
    "First, you'll have seen that the syntax for assigning a new column as part of a method chain is relatively fussy, using as it does the `new_column=lambda x: func(x['old_column])` syntax. If you already have a dataframe named `df`, **pyjanitor** gives you the option to create new columns like so (using an example from the documentation):\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "\n",
    "df = (\n",
    "    df.add_columns(\n",
    "        prop_late_departures=df.num_departing_late / df.total_num_trips,\n",
    "        prop_late_arrivals=df.num_arriving_late / df.total_num_trips\n",
    "    )\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's great about this is not only that it's cleaner to read, but that all you need do to get the functionality is to import the pyjanitor library and the extra functions will appear magically as options to use on the usual pandas dataframes.\n",
    "\n",
    "Pyjanitor isn't just about method chaining--it does a lot of other things too, like introducing a bunch of convenience commands with easily understandable verbs behind them. (Though do note it has a hefty download size.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping data\n",
    "\n",
    "The main options for reshaping data are `pivot`, `melt`, `stack`, `unstack`, `pivot_table`, `get_dummies`, `cross_tab`, and `explode`. We’ll look at some of these here.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting data from tidy to, err, untidy\n",
    "\n",
    "At the start of this chapter, I said you should use tidy data--one row per observation, one column per variable--whenever you can. But there are times when you will want to take your lovingly prepared tidy data and pivot it into a wider format. `pivot` and `pivot_table` help you to do that.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_pivot.png)\n",
    "\n",
    "This can be especially useful for time series data, where operations like `shift` or `diff` are typically applied assuming that an entry in one row follows (in time) from the one above. Here's an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    \"value\": np.random.randn(20),\n",
    "    \"variable\": [\"A\"] * 10 + [\"B\"] * 10,\n",
    "    \"date\": (\n",
    "        list(pd.date_range(\"1/1/2000\", periods=10, freq=\"M\"))\n",
    "        + list(pd.date_range(\"1/1/2000\", periods=10, freq=\"M\"))\n",
    "    ),\n",
    "}\n",
    "df = pd.DataFrame(data, columns=[\"date\", \"variable\", \"value\"])\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we just run `shift` on this, it's going to shift variable B's and A's together. So we pivot to a wider format (and then we can shift safely)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.pivot(index=\"date\", columns=\"variable\", values=\"value\").shift(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Melt\n",
    "\n",
    "`melt` can help you go from untidy to tidy data (from wide data to long data), and is a *really* good one to remember. Of course, I have to look at the documentation every single time myself, but I'm sure you'll do better.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_melt.png)\n",
    "\n",
    "Here's an example of it in action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"first\": [\"John\", \"Mary\"],\n",
    "        \"last\": [\"Doe\", \"Bo\"],\n",
    "        \"height\": [5.5, 6.0],\n",
    "        \"weight\": [130, 150],\n",
    "    }\n",
    ")\n",
    "print(\"\\n Unmelted: \")\n",
    "print(df)\n",
    "print(\"\\n Melted: \")\n",
    "df.melt(id_vars=[\"first\", \"last\"], var_name=\"quantity\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you don't wan the headscratching of melt, there's also `wide_to_long`, which is really useful for typical data cleaning cases where you have data like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"A1970\": {0: \"a\", 1: \"b\", 2: \"c\"},\n",
    "        \"A1980\": {0: \"d\", 1: \"e\", 2: \"f\"},\n",
    "        \"B1970\": {0: 2.5, 1: 1.2, 2: 0.7},\n",
    "        \"B1980\": {0: 3.2, 1: 1.3, 2: 0.1},\n",
    "        \"X\": dict(zip(range(3), np.random.randn(3))),\n",
    "        \"id\": dict(zip(range(3), range(3))),\n",
    "    }\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "i.e. data where there are different variables and time periods across the columns. Wide to long is going to let us give info on what the stubnames are ('A', 'B'), the name of the variable that's always across columns (here, a year), any values (X here), and an id column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.wide_to_long(df, [\"A\", \"B\"], i=\"id\", j=\"year\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stack and unstack\n",
    "\n",
    "Stack, `stack()` is a shortcut for taking a single type of wide data variable from columns and turning it into a long form dataset, but with an extra index.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_stack.png)\n",
    "\n",
    "Unstack, `unstack()` unsurprisingly does the same operation, but in reverse.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_unstack.png)\n",
    "\n",
    "Let's define a multi-index dataframe to demonstrate this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tuples = list(\n",
    "    zip(\n",
    "        *[\n",
    "            [\"bar\", \"bar\", \"baz\", \"baz\", \"foo\", \"foo\", \"qux\", \"qux\"],\n",
    "            [\"one\", \"two\", \"one\", \"two\", \"one\", \"two\", \"one\", \"two\"],\n",
    "        ]\n",
    "    )\n",
    ")\n",
    "index = pd.MultiIndex.from_tuples(tuples, names=[\"first\", \"second\"])\n",
    "df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=[\"A\", \"B\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's stack this to create a tidy dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.stack()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's see unstack but, instead of unstacking the 'A', 'B' variables we began with, let's unstack the 'first' column by passing `level=0` (the default is to unstack the innermost index). This diagram shows what's going on:\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_unstack_0.png)\n",
    "\n",
    "And here's the code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.unstack(level=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get dummies\n",
    "\n",
    "This is a really useful reshape command for when you want (explicit) dummies in your dataframe. When running simple regressions, you can achieve the same effect by declaring the column only be included as a fixed effect, but there are some machine learning packages where converting to dummies may be easier.\n",
    "\n",
    "Here's an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\"group_var\": [\"group1\", \"group2\", \"group3\"], \"B\": [\"c\", \"c\", \"b\"], \"C\": [1, 2, 3]}\n",
    ")\n",
    "print(df)\n",
    "\n",
    "pd.get_dummies(df, columns=[\"group_var\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Combining data\n",
    "\n",
    "**pandas** has a really rich set of options for combining one or more dataframes. The two most important are concatenate and merge.\n",
    "\n",
    "### Concatenate\n",
    "\n",
    "If you have two or more dataframes with the same index or the same columns, you can glue them together into a single dataframe using `pd.concat`. \n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/08_concat_row.svg)\n",
    "\n",
    "For the same columns, pass `axis=0` to glue the index together; for the same index, pass `axis=1` to glue the columns together. The concatenate function will typically be used on a list of dataframes.\n",
    "\n",
    "If you want to track where the original data came from in the final dataframe, use the `keys` keyword.\n",
    "\n",
    "Here's an example using data on two different states' populations that also makes uses of the `keys` option:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_url = \"http://www.stata-press.com/data/r14/\"\n",
    "state_codes = [\"ca\", \"il\"]\n",
    "end_url = \"pop.dta\"\n",
    "\n",
    "# This grabs the two dataframes, one for each state\n",
    "list_of_state_dfs = [pd.read_stata(base_url + state + end_url) for state in state_codes]\n",
    "\n",
    "# Concatenate the list of dataframes\n",
    "df = pd.concat(list_of_state_dfs, keys=state_codes, axis=0)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge\n",
    "\n",
    "There are so many options for merging dataframes using `pd.merge(left, right, on=..., how=...` that we won't be able to cover them all here. The most important features are: the two dataframes to be merged, what variables (aka keys) to merge on (and these can be indexes) via `on=`, and *how* to do the merge (eg left, right, outer, inner) via `how=`. This diagram shows an example of a merge using keys from the left-hand dataframe:\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/08_merge_left.svg)\n",
    "\n",
    "The `how=` keyword works in the following ways:\n",
    "- `how='left'` uses keys from the left dataframe only to merge.\n",
    "- `how='right'` uses keys from the right dataframe only to merge.\n",
    "- `how='inner'` uses keys that appear in both dataframes to merge.\n",
    "- `how='outer'` uses the cartesian product of keys in both dataframes to merge on.\n",
    "\n",
    "Let's see examples of some of these:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame(\n",
    "    {\n",
    "        \"key1\": [\"K0\", \"K0\", \"K1\", \"K2\"],\n",
    "        \"key2\": [\"K0\", \"K1\", \"K0\", \"K1\"],\n",
    "        \"A\": [\"A0\", \"A1\", \"A2\", \"A3\"],\n",
    "        \"B\": [\"B0\", \"B1\", \"B2\", \"B3\"],\n",
    "    }\n",
    ")\n",
    "right = pd.DataFrame(\n",
    "    {\n",
    "        \"key1\": [\"K0\", \"K1\", \"K1\", \"K2\"],\n",
    "        \"key2\": [\"K0\", \"K0\", \"K0\", \"K0\"],\n",
    "        \"C\": [\"C0\", \"C1\", \"C2\", \"C3\"],\n",
    "        \"D\": [\"D0\", \"D1\", \"D2\", \"D3\"],\n",
    "    }\n",
    ")\n",
    "# Right merge\n",
    "pd.merge(left, right, on=[\"key1\", \"key2\"], how=\"right\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the key combination of K2 and K0 did not exist in the left-hand dataframe, and so its entries in the final dataframe are NaNs. But it *does* have entries because we chose the keys from the right-hand dataframe.\n",
    "\n",
    "What about an inner merge?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on=[\"key1\", \"key2\"], how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we see that the combination K2 and K0 are excluded because they didn't exist in the overlap of keys in both dataframes.\n",
    "\n",
    "Finally, let's take a look at an outer merge that comes with some extra info via the `indicator` keyword:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on=[\"key1\", \"key2\"], how=\"outer\", indicator=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see that the products of all key combinations are here. The `indicator=True` option has caused an extra column to be added, called '_merge', that tells us which dataframe the keys on that row came from.\n",
    "\n",
    "For more on the options, see the **pandas** [merging documentation](https://pandas.pydata.org/docs/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (Advanced) alternatives to **pandas**\n",
    "\n",
    "Feel free to skip this section if you're just interested in getting going as quickly as possible.\n",
    "\n",
    "**pandas** isn't the only game in town, not by a long way--though it's by far the best supported and the most fully featured. But it's always good to have options--or, put another way, options have value! Other dataframe libraries may have a syntax that you prefer or provide a speed-up (perhaps in certain situations, for example when working with very large datasets).\n",
    "\n",
    "If you're specifically interested in how different dataframe options perform on increasingly large datasets, take a look at the benchmarks [here](https://h2oai.github.io/db-benchmark/).\n",
    "\n",
    "Here's a quick run-through of some alternatives to **pandas**:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Datatable\n",
    "\n",
    "[**datatable**](https://datatable.readthedocs.io/en/latest/) is another dataframe based way to do analysis, and it has quite a different syntax to **pandas** for data manipulation. **datatable** is very fast, not the fastest dataframe option out there, but it holds its own. Its other major advantage is that it is comfortable running on extremely large (think 50GB) datasets. Most other dataframe packages (including in other languages) cannot cope with this.\n",
    "\n",
    "I am indebted to the very well-written datatable documentation for the rest of this demonstration.\n",
    "\n",
    "In datatable, almost all operations are achieved via so-called 'square-bracket notation'. Operations with a (data) Frame are almost all expressed in the form\n",
    "\n",
    "```python\n",
    "DT[i, j, ...]\n",
    "```\n",
    "\n",
    "where `DT` is a datatable dataframe, `i` is the row selector, `j` is the column selector, and `...` is a space that could be filled by other commands (potentially several). However, `i` and `j` aren't just positional, as they might be in a **numpy** array or a list, they do a lot more than that. \n",
    "\n",
    "Datatable allows `i` to be anything that can conceivably be interpreted as a row selector: an integer to select a row, a slice, a range, a list of integers, a list of slices, an expression, a boolean-valued Frame, an integer-valued Frame, an integer **numpy** array, a generator, and more.\n",
    "\n",
    "Likewise, `j` is also really versatile. It can be used to select a column by name or position, but it will also accept a list of columns, a slice, a list of booleans indicating which columns to pick, an expression, a list of expressions, and a dictionary of expressions. The j expression can even be a python type (such as int or dt.float32), selecting all columns matching that type!\n",
    "\n",
    "To change an entry (entries), you can use `DT[i, j] = new_value`, and to remove it (or them), it's `del DT[i, j]`.\n",
    "\n",
    "It's probably going to be easiest to see some examples, so let's do a quick run through of some functions with the storms dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datatable import dt, f, by, g, join, sort, update, ifelse\n",
    "\n",
    "DT = dt.fread(\"https://vincentarelbundock.github.io/Rdatasets/csv/dplyr/storms.csv\")\n",
    "\n",
    "DT.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes, that's right, you're seeing columns colour coded by data type! Otherwise, what you can see here is that the syntax so far looks quite similar but don't worry because it's about to get weird.\n",
    "\n",
    "The square brackets accept expressions, i.e. functions. This is achieved through a special import, simply denoted `f`, that says do this thing to the current datatable. For example, to filter by a certain value in the 'status' column (to avoid overloading with data, I'll just use head to show the first few rows):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[f.status == \"tropical storm\", :].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get rid of some columns we're not using right now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del DT[:, \"year\":\"long\"]\n",
    "DT.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, to select only those columns that are strings:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, str].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or to select only those columns that contain 'diameter'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, [x for x in DT.names if \"diameter\" in x]].tail(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a new column based on an existing one and just look at the last few columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, update(atmospheres=f.pressure / 1013.0)]\n",
    "DT[:, -4:].tail(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To sort all values by multiple columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, :, sort(\"wind\", \"pressure\", reverse=[True, True])].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And to perform group-by operations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, dt.mean(f.wind), by(\"status\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And, finally, one that I always find of great practical use--the within-group transform, with the new column going back into the original data(frame/table). In **pandas**, this is achieved by `transform`, here we use `extend`. Let's demonstrate by showing the maximum wind speed by status group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, f[:].extend({\"max_wind\": dt.min(f.wind)}), by(\"status\")].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### cuDF\n",
    "\n",
    "[**cuDF**](https://github.com/rapidsai/cudf) is still under development and so doesn't yet have all the features of **pandas**. It's a Python GPU DataFrame library built on the blisteringly fast Apache Arrow columnar memory format. The parts of the library that have been implemented follow the same commands and structure as **pandas**, so it should be easy to use it should you need to.\n",
    "\n",
    "**cuDF** is *the* fastest dataframe library out there in any of Python, Julia, Rust, or R with the caveats that:\n",
    "- this only applies to in-memory datasets, ie datasets smaller than a few GB\n",
    "- it doesn't yet do everything\n",
    "- it's only currently available on the Linux operating system\n",
    "- you need a GPU (and associated software) to use it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Polars\n",
    "\n",
    "[**Polars**]() is almost as fast as **cuDF**, but is further down the development path. It also uses Apache Arrow as backend. It currently consists of an 'eager' (for datasets smaller than approximately a few GB) interface that's very similar to **pandas** and a 'lazy' interface (don't worry if you don't know what that means, it's a big data thing) that is somewhat similar to spark (a big data tool). **Polars** is built on the Rust language. It's particularly effective at merging datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plydata\n",
    "\n",
    "[Plydata](https://plydata.readthedocs.io/en/stable/index.html) uses the syntax of the **dplyr** package in the R statistical language (the package is a really important part of the data ecosystem in R). It's built around method chaining and everything being in a tidy format, which has pros and cons. Largely, the benefits are in readability and a smaller number of commands to cover the ground that you need to. The downsides are that it plays best with tidy data and when chained methods go wrong it can be more difficult to find out what the issue is.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### dfplyr\n",
    "\n",
    "[dfplyr](https://github.com/kieferk/dfply) also follows the syntax of the **dplyr** package in R. *Note* that it does not appear to be under active development."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Review\n",
    "\n",
    "If you know:\n",
    "\n",
    "- ✅ what tidy data are;\n",
    "- ✅ how to create series and dataframes with different datatypes;\n",
    "- ✅ how to access values in series and dataframes;\n",
    "- ✅ how to perform operations on columns;\n",
    "- ✅ how to chain methods;\n",
    "- ✅ how to reshape data;\n",
    "- ✅ how to combine different dataframes; and\n",
    "- ✅ what some alternatives to **pandas** are\n",
    "\n",
    "then you are well on your way to becoming a data analysis master!\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "kernelspec": {
   "display_name": "codeforecon",
   "language": "python",
   "name": "codeforecon"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
